package com.bw.mapper;

import com.bw.bean.Vo;
import com.bw.bean.Vo1;
import com.bw.bean.Vo2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface VoMapper {
    @Select("select s.name,\n" +
            "       sum(oi.original_amount) money,\n" +
            "       count(oi.id) num,\n" +
            "       sum(distinct oi.customer_id) num1,\n" +
            "       sum(oi.actual_amount) sum,\n" +
            "       sum(p.amount) sum1\n" +
            "       from order_info oi\n" +
            "left join payment p on oi.id = p.order_info_id\n" +
            "left join shop s on oi.shop_id = s.id\n" +
            "where p.status=5\n" +
            "group by s.name;")
    List<Vo> getList1();

    @Select("select curdate() day,\n" +
            "       1 time,\n" +
            "       sum(if(s.type=1,p.reduce_amount,0)) sum1,\n" +
            "       sum(if(s.type=2,p.reduce_amount,0)) sum2,\n" +
            "       sum(p.reduce_amount) sum3\n" +
            "       from order_info oi\n" +
            "left join shop s on oi.shop_id = s.id\n" +
            "left join promotion p on oi.promotion_id = p.id\n" +
            "where timestampdiff(day ,p.create_time,now())<1\n" +
            "union\n" +
            "select curdate() day,\n" +
            "       7 time,\n" +
            "       sum(if(s.type=1,p.reduce_amount,0)) sum1,\n" +
            "       sum(if(s.type=2,p.reduce_amount,0)) sum2,\n" +
            "       sum(p.reduce_amount) sum3\n" +
            "from order_info oi\n" +
            "left join shop s on oi.shop_id = s.id\n" +
            "left join promotion p on oi.promotion_id = p.id\n" +
            "where timestampdiff(day ,p.create_time,now())<7\n" +
            "union\n" +
            "select curdate() day,\n" +
            "       30 time,\n" +
            "       sum(if(s.type=1,p.reduce_amount,0)) sum1,\n" +
            "       sum(if(s.type=2,p.reduce_amount,0)) sum2,\n" +
            "       sum(p.reduce_amount) sum3\n" +
            "from order_info oi\n" +
            "left join shop s on oi.shop_id = s.id\n" +
            "left join promotion p on oi.promotion_id = p.id\n" +
            "where timestampdiff(day ,p.create_time,now())<30;")
    List<Vo1> getList2();

    @Select("select psp.name,sum(if(oi.rating=5,1,0))/count(oi.id)*100 value from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_info_id\n" +
            "left join product_sku psk on od.product_sku_id = psk.id\n" +
            "left join product_spu psp on psk.product_spu_id = psp.id\n" +
            "where psp.name is not null\n" +
            "group by psp.name;")
    List<Vo2> getList3();

    @Select("select psp.name,avg(oi.rating) value from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_info_id\n" +
            "left join product_sku psk on od.product_sku_id = psk.id\n" +
            "left join product_spu psp on psk.product_spu_id = psp.id\n" +
            "where psp.name is not null\n" +
            "group by psp.name;")
    List<Vo2> getList4();


    @Select("select hour(create_time) name, sum(original_amount) value from order_info\n" +
            "group by hour(create_time);")
    List<Vo2> getList5();
}
